
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Scanner;


/**
 *
 * @author tromblej0657
 */
public class CreateSFXTDB
{

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) throws IOException
    {
        ArrayList<String> parts = new ArrayList<>();
        
        try{
                File file = new File("CSV//Characters.csv");
                Scanner inFile = new Scanner(file);
                Scanner charData;
                
                
                String[] characters = inFile.nextLine().split(", ");
                
                final String DB_URL = "jdbc:derby:SFXTCast;create=true";
                
                try {
                    Connection conn = DriverManager.getConnection(DB_URL);
                    
                    Statement stmt = conn.createStatement();
                    String query;
                    
                    query = "CREATE TABLE Glossary ("
                            + "Label Char(20),"
                            + "Description Char(50))";
                    stmt.execute(query);
             
                    System.out.println(query);
                    parseGlossary(parts);                   
                    
                    query = "CREATE TABLE Misc.Data ("
                            + "CharacterName Char(25),"
                            + "Health Char(4),"
                            + "ForwardWalk Char(5),"
                            + "Backwalk Char(5),"
                            + "ForwardDash CHar(2),"
                            + "Backdash Char(2),"
                            + "Prejump Char(1),"
                            + "DiagonalJump Char(20),"
                            + "NeutralJump Char(20),"
                            + "ChargeCancelDelay Char(5))";
                    stmt.execute(query);
                    
                    System.out.println(query);
                    parseMiscData(parts);
                    
                    for(int index = 0; index < characters.length; index++)
                    {
                        query = "CREATE TABLE " + characters[index] + " ("
                                + "Move Char(50),"
                                + "Command Char(100),"
                                + "Hit Char(30),"
                                + "Damage Char(50),"
                                + "Startup Char(30),"
                                + "Active Char(100),"
                                + "Recovery Char(50),"
                                + "HitAdv Char(30),"
                                + "BlockAdv Char(30),"
                                + "Cancel Char(50),"
                                + "Notes Char(250))";
                        stmt.execute(query);
                        
                        System.out.println(query);
                        parseCSV(characters[index], parts);
                    }
                }
                catch(SQLException | FileNotFoundException ex) {
                    System.out.println("ERROR; " + ex.getMessage());
                }
        }
        catch(FileNotFoundException e) {
            System.out.println("ERROR: " + e.getMessage());
        }
    }
    
    private static void parseCSV(String character, 
            ArrayList<String> parts) throws IOException
    {
        String fileName = "CSV//" + character + ".csv";
        File csv = new File(fileName);
        
        try (Scanner inFile = new Scanner(csv)) {
            String line;
            
            while(inFile.hasNext()) {
                line = inFile.nextLine();
                if(!(line.equals("")))
                {
                    parseLine(line, parts);
                    populateCharacters(character, parts);
                    parts.clear();
                }

            }
        }
    }
    
    private static void parseGlossary(ArrayList<String> parts) 
            throws FileNotFoundException
    {
        String fileName = "CSV//Glossary.csv";
        File csv = new File(fileName);
        
        try (Scanner inFile = new Scanner(csv)) {
            String line;
            
            while(inFile.hasNext()) {
                line = inFile.nextLine();
                if(!(line.equals("")))
                {
                    parseLine(line, parts);
                    populateGlossary(parts);
                    parts.clear();
                }

            }
        }
    }
    
    private static void parseMiscData(ArrayList<String> parts)
            throws FileNotFoundException
    {
        String fileName = "CSV//Misc Data.csv";
        File csv = new File(fileName);
        
        try (Scanner inFile = new Scanner(csv)) {
            String line;
            
            while(inFile.hasNext()) {
                line = inFile.nextLine();
                if(!(line.equals("")))
                {
                    parseLine(line, parts);
                    populateMiscData(parts);
                    parts.clear();
                }

            }
        }
    }
    
    private static void parseLine(String line, ArrayList<String> parts) {
        
        int index = line.indexOf("\"");
        
        if(index == -1)
        {
            String[] items = line.split(",");
            for(int i = 0; i < items.length; i++)
            {
                parts.add(items[i]);
            }
        }
        else if(index == 0)
        {
            String right = line.substring(1);
            int nextIndex = right.indexOf("\"");
            String mid = right.substring(0, nextIndex);
            parts.add(mid);
            if(!(right.substring(nextIndex + 1).equals("")))
            {
                if(right.substring(nextIndex + 1, nextIndex + 2).equals(","))
                {
                    String outer = right.substring(nextIndex + 2);
                    parseLine(outer, parts);
                }
            }
        }
        else
        {
            String left = line.substring(0, index);
            String[] items = left.split(",");
            for(int i = 0; i < items.length; i++)
            {
                parts.add(items[i]);
            }
            String right = line.substring(index + 1);
            int nextIndex = right.indexOf("\"");
            String mid = right.substring(0, nextIndex);
            parts.add(mid);
            if(!(right.substring(nextIndex + 1).equals("")))
            {
                if(right.substring(nextIndex + 1, nextIndex + 2).equals(","))
                {
                    String outer = right.substring(nextIndex + 2);
                    parseLine(outer, parts);
                }
            }
        }   
    }
    
    private static void populateCharacters(String character,
            ArrayList<String> parts)
    {
        final String DB_URL = "jdbc:derby:SFXTCast";
        
        try {
            Connection conn = DriverManager.getConnection(DB_URL);
            
            Statement stmt = conn.createStatement();
            String sqlStatment;
            
            sqlStatment = "INSERT INTO " + character  +" "
                    + "(Move, Command, Hit, Damage, Startup, Active, "
                    + "Recovery, HitAdv, BlockAdv, Cancel, Notes) "
                    + "VALUES ('"
                    + parts.get(0) + "', '"
                    + parts.get(1) + "', '"
                    + parts.get(2) + "', '"
                    + parts.get(3) + "', '"
                    + parts.get(4) + "', '"
                    + parts.get(5) + "', '"
                    + parts.get(6) + "', '"
                    + parts.get(7) + "', '"
                    + parts.get(8) + "', '"
                    + parts.get(9) + "', '"
                    + parts.get(10) + "')";
            
            
            stmt.executeUpdate(sqlStatment);
            System.out.println("Insert Successfull");
        }
        catch(Exception ex)
        {
            System.out.println("ERROR: " + ex.getMessage());
        }
    }
    
    private static void populateGlossary(ArrayList<String> parts)
    {
        final String DB_URL = "jdbc:derby:SFXTCast";
        
        try {
            Connection conn = DriverManager.getConnection(DB_URL);
            
            Statement stmt = conn.createStatement();
            String sqlStatment;
            
            sqlStatment = "INSERT INTO Glossary "
                    + "(Label, Description) "
                    + "VALUES ('"
                    + parts.get(0) + "', '"
                    + parts.get(1) + "')";
            
            
            stmt.executeUpdate(sqlStatment);
            System.out.println("Insert Successfull");
        }
        catch(Exception ex)
        {
            System.out.println(parts.toString());
            System.out.println("ERROR: " + ex.getMessage());
        }
    }
       
    private static void populateMiscData(ArrayList<String> parts)
    {
        final String DB_URL = "jdbc:derby:SFXTCast";
        
        try {
            Connection conn = DriverManager.getConnection(DB_URL);
            
            Statement stmt = conn.createStatement();
            String sqlStatment;
            
            sqlStatment = "INSERT INTO Misc.Data "
                    + "(CharacterName, Health, ForwardWalk, Backwalk, "
                    + "ForwardDash, BackDash, PreJump, DiagonalJump, "
                    + "NeutralJump, ChargeCancelDelay) "
                    + "VALUES ('"
                    + parts.get(0) + "', '"
                    + parts.get(1) + "', '"
                    + parts.get(2) + "', '"
                    + parts.get(3) + "', '"
                    + parts.get(4) + "', '"
                    + parts.get(5) + "', '"
                    + parts.get(6) + "', '"
                    + parts.get(7) + "', '"
                    + parts.get(8) + "', '"
                    + parts.get(9) + "')";
            
            
            stmt.executeUpdate(sqlStatment);
            System.out.println("Insert Successfull");
        }
        catch(Exception ex)
        {
            System.out.println("ERROR: " + ex.getMessage());
        }
    }
}
